package com.bw.mapper;

import com.bw.bean.Vo1;
import com.bw.bean.Vo2;
import com.bw.bean.Vo3;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface Vo1Mapper {

    @Select("select hour(create_time) name,count(*) value\n" +
            "from user_chapter_process\n" +
            "group by hour(create_time) order by hour(create_time)")
    List<Vo1> getlist1();

    @Select("select tp.paper_title name,\n" +
            "       count(te.user_id) rs,\n" +
            "       avg(te.score) jf,\n" +
            "       avg(te.duration_sec) js\n" +
            "from test_paper tp\n" +
            "    left join test_exam te on tp.id = te.paper_id\n" +
            "group by tp.paper_title")
    List<Vo2> getlist2();

    @Select("select count(ui.id)\n" +
            "from payment_info pi\n" +
            "    left join order_info oi on oi.id = pi.order_id\n" +
            "    left join order_detail od on oi.id = od.order_id\n" +
            "    left join user_info ui on ui.id = od.user_id\n" +
            "where timestampdiff(year,pi.create_time,now())=1")
    int getlist3();

    @Select("select ci.course_name name,\n" +
            "       avg(ri.review_stars) jf,\n" +
            "       count(distinct ri.user_id) rs,\n" +
            "       sum(if(ri.review_stars=5,1,0)) hp,\n" +
            "       sum(if(ri.review_stars=5,1,0))/count(distinct ri.user_id) pv\n" +
            "from course_info ci\n" +
            "    left join review_info ri on ci.id = ri.course_id\n" +
            "group by ci.course_name")
    List<Vo3> getlist4();
}
